﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Data.Entity.Validation;

namespace Qu.SeniorProject.Model
{
    public class ProjectRepository
    {

        public static DateTime? GetProjectExaminationDate()
        {
            var spDB = new SeniorProjectEntities();
            return spDB.ProjectPeriods.Where(p => p.Status == "Current").FirstOrDefault().ProjectExaminationDate;
        }

        public static void SetProjectAsComplete(int projectID)
        {
            var spDB = new SeniorProjectEntities();
            var proj = spDB.Projects.Where(p => p.ProjectId == projectID).FirstOrDefault();

            SendConfirmation(proj);

            proj.IsComplete = true;
            spDB.SaveChanges();
        }

        public static void SetIsNominatedBySupervisor(int projectID, bool isNominatedBySupervisor)
        {
            var spDB = new SeniorProjectEntities();
            var proj = spDB.Projects.Where(p => p.ProjectId == projectID).FirstOrDefault();
            proj.IsNominatedBySupervisor = isNominatedBySupervisor;
            spDB.SaveChanges();
        }

        public static void  SetIsNominatedByExaminer1 (int projectID, bool isNominatedByExaminer1)
        {
            var spDB = new SeniorProjectEntities();
            var proj = spDB.Projects.Where(p => p.ProjectId == projectID).FirstOrDefault();
            proj.IsNominatedByExaminer1 = isNominatedByExaminer1;
            spDB.SaveChanges();
        }

        public static void SetIsNominatedByExaminer2(int projectID, bool isNominatedByExaminer2)
        {
            var spDB = new SeniorProjectEntities();
            var proj = spDB.Projects.Where(p => p.ProjectId == projectID).FirstOrDefault();
            proj.IsNominatedByExaminer2 = isNominatedByExaminer2;
            spDB.SaveChanges();
        }

        public static List<ProjectSummary> GetProjectsSummary(string filter = "AllProjects", int periodID = 0)
        {
            var spDB = new SeniorProjectEntities();
            var projects = spDB.Database.SqlQuery<ProjectSummary>(string.Format("GetProjectsSummary @Filter='{0}', @PeriodID={1}", filter, periodID)).ToList();
            projects.ForEach(p => p.FixMultilineFields());
            return projects;
        }

        public static List<Project> GetProjects()
        {
            var spDB = new SeniorProjectEntities();
            var projects = spDB.Projects.ToList();

            //ToDo: Comment this out later
            foreach (var proj  in projects)
            {
                //Set Supervisor
                var projectFaculty = spDB.ProjectFaculties.Where(f => f.ProjectID == proj.ProjectId
                            && f.RoleID == "Supervisor").FirstOrDefault();

                if (projectFaculty != null)
                    proj.SupervisorID = projectFaculty.FacultyID;
            }

            return projects;
        }

        public static List<Faculty> GetAvailableSupervisors()
        {
            var spDB = new SeniorProjectEntities();
            var supervisors = spDB.Database.SqlQuery<Faculty>("GetAvailableSupervisors").ToList();
            return supervisors;
        }

        public static Project GetProject(int projectID)
        {
            var spDB = new SeniorProjectEntities();
            Project proj = spDB.Projects.Where(p => p.ProjectId == projectID).FirstOrDefault();

            var studentIDs = new List<string>();

            foreach (var student in proj.Students)
                studentIDs.Add(student.StudentId);

            proj.StudentIDs = studentIDs;

            ////ToDo: Quick fix - Improve this
            //ProjectPeriod projPeriod = spDB.ProjectPeriods.Where(p => p.PeriodId == proj.PeriodId).FirstOrDefault();
            //proj.UpdatedBy = projPeriod.PeriodDescription;

            //Set Supervisor
            var projectFaculty = spDB.ProjectFaculties.Where(f => f.ProjectID == projectID
                        && f.RoleID == "Supervisor").FirstOrDefault();

            if (projectFaculty != null)
                proj.SupervisorID = projectFaculty.FacultyID;

            //    proj.Supervisor = spDB.Faculties.Where(f => f.FacultyId == projectFaculty.FacultyID).FirstOrDefault().Name;
            //}

            //Set Co-Supervisor
            projectFaculty = spDB.ProjectFaculties.Where(f => f.ProjectID == projectID
                        && f.RoleID == "CoSupervisor").FirstOrDefault();

            if (projectFaculty != null)
                proj.CoSupervisorID = projectFaculty.FacultyID;

            //if (!string.IsNullOrEmpty(proj.Supervisor))
            //    proj.Supervisor = Regex.Replace(proj.Supervisor, @"\r\n", "<br/>", RegexOptions.Multiline);

            return proj;
        }

        private static string GetProjectSupervisor(int projectID)
        {
            var spDB = new SeniorProjectEntities();
            return spDB.Database.SqlQuery<string>(string.Format("Select dbo.GetSupervisorsByProjectID({0})", projectID)).FirstOrDefault();
        }

        public static int GetNextYearProjectPeriodId()
        {
            var spDB = new SeniorProjectEntities();
            return spDB.Database.SqlQuery<int>(string.Format("Select dbo.GetNextYearProjectPeriodId()")).FirstOrDefault();
        }

        public static bool CanAddProject(int facultyId)
        {
            int periodId = GetNextYearProjectPeriodId();
            var spDB = new SeniorProjectEntities();
            return spDB.Database.SqlQuery<bool>(string.Format("Select dbo.CanAddProject({0}, {1})", facultyId, periodId)).FirstOrDefault();
        }

        public static string GetStudentIDsByProjectID(int projectID)
        {
            var spDB = new SeniorProjectEntities();
            return spDB.Database.SqlQuery<string>(string.Format("Select dbo.GetStudentIDsByProjectID({0})", projectID)).FirstOrDefault();
        }

        public static bool IsProjectTeamSameGender(IEnumerable<string> students)
        {
            if (students == null || students.Count() <= 1)
                return true;

            StringBuilder sb = new StringBuilder();

            foreach (var student in students)
            {
                if (sb.Length > 0)
                    sb.Append(",");

                sb.Append(student);   
            }

            var spDB = new SeniorProjectEntities();
            return spDB.Database.SqlQuery<bool>(string.Format("Select dbo.IsProjectTeamSameGender('{0}')", sb.ToString())).FirstOrDefault();
        }

        public static ProjectSummary GetProjectSummary(int projectID, int facultyID)
        {
            var spDB = new SeniorProjectEntities();
            var project = spDB.Database.SqlQuery<ProjectSummary>(string.Format("GetProjectsSummary @Filter='{0}', @projectID={1}, @facultyID={2}", 
                    "ProjectByProjectID", projectID, facultyID)).FirstOrDefault();
            project.FixMultilineFields();

            //Do not show exact demo time to students
            if (facultyID == 0 && project.PosterTime != null)
                project.PosterTime = "2 to 4 PM"; 

            return project;
        }

        public static List<ProjectSummary> GetSupervisedProjects(int facultyID, int periodID = 0)
        {
            var spDB = new SeniorProjectEntities();
            var projects = spDB.Database.SqlQuery<ProjectSummary>(string.Format("GetProjectsSummary @Filter='{0}', @FacultyID={1}, @PeriodID={2}", "ProjectsByFaculty", facultyID, periodID)).ToList();
            projects.ForEach(p => p.FixMultilineFields());
            return projects;
        }

        public static List<Project> GetProjectsToReview(int facultyID)
        {
            var spDB = new SeniorProjectEntities();
            return spDB.Database.SqlQuery<Project>("GetProjectsToReviewByFaculty @facultyID", new SqlParameter("facultyID", facultyID)).ToList();
        }

        public static List<Student> GetProjectStudents(int projectID)
        {
            var spDB = new SeniorProjectEntities();
            return spDB.Database.SqlQuery<Student>("GetProjectStudents @projectID", new SqlParameter("projectID", projectID)).ToList();
        }

        public static int GetStudentProjectID(string studentID)
        {
            var spDB = new SeniorProjectEntities();
            return spDB.Database.SqlQuery<int>("GetStudentProjectID @studentID", new SqlParameter("studentID", studentID)).FirstOrDefault();
        }


        public static void UpdateProject(Project project)
        {
            try
            {
                var spDB = new SeniorProjectEntities();
                Project proj = spDB.Projects.Where(p => p.ProjectId == project.ProjectId).FirstOrDefault();


                if (project.StudentIDs == null || project.SupervisorID == null || project.SupervisorID.Value == 0)
                    project.PeriodId = 0;
                else if (project.PeriodId == 0 && project.StudentIDs != null && project.SupervisorID != null && project.SupervisorID.Value != 0)
                    project.PeriodId = ProjectRepository.GetNextYearProjectPeriodId();

                SetCollaboratorDetails(project);

                proj.ProjectTitle = project.ProjectTitle;
                proj.ProjectDescription = project.ProjectDescription;
                proj.ProjectTasks = project.ProjectTasks;
                proj.SkillsRequired = project.SkillsRequired;
                proj.ResourcesNeeded = project.ResourcesNeeded;
                proj.UpdatedBy = project.UpdatedBy;
                proj.UpdatedDate = project.UpdatedDate;
                proj.PeriodId = project.PeriodId;

                proj.InCollaborationWith = project.InCollaborationWith;
                proj.CollaboratorOrganization = project.CollaboratorOrganization;
                proj.CollaboratorContactName = project.CollaboratorContactName;
                proj.CollaboratorContactEmail = project.CollaboratorContactEmail;
                proj.CollaboratorContactPhone = project.CollaboratorContactPhone;
                proj.CollaboratorContactMobile = project.CollaboratorContactMobile;
                
                spDB.SaveChanges();

                var sendConfirmationEmail = SetProjectSupervisor(project.ProjectId, project.SupervisorID);
                SetProjectSupervisor(project.ProjectId, project.CoSupervisorID, "CoSupervisor");

                if ((UpdateProjectStudents(project) || sendConfirmationEmail) && project.StudentIDs != null)
                    SendProjectRegistrationConfirmation(project);
            }
            catch (DbEntityValidationException dbEx)
            {
                foreach (var validationErrors in dbEx.EntityValidationErrors)
                {
                    var sb = new StringBuilder();
                    foreach (var validationError in validationErrors.ValidationErrors)
                    {
                      sb.AppendFormat("Property: {0} Error: {1}\n", validationError.PropertyName, validationError.ErrorMessage);
                    }
                    throw new Exception(sb.ToString());
                }
            }
        }

        public static int AddProject(Project project)
        {
            var spDB = new SeniorProjectEntities();

            if (project.StudentIDs != null && project.SupervisorID != null && project.SupervisorID.Value != 0)
                project.PeriodId = ProjectRepository.GetNextYearProjectPeriodId();
            else
                project.PeriodId = 0;
            
            SetCollaboratorDetails(project);

            Project proj = spDB.Projects.Add(project);

            spDB.SaveChanges();

            SetProjectSupervisor(project.ProjectId, project.SupervisorID);
            SetProjectSupervisor(project.ProjectId, project.CoSupervisorID, "CoSupervisor");

            if (project.StudentIDs != null)
            {
                foreach (var studentID in project.StudentIDs)
                    AddStudentToProject(proj.ProjectId, studentID);

                SendProjectRegistrationConfirmation(proj);
            }

            return proj.ProjectId;
        }

        private static void SetCollaboratorDetails(Project project)
        {
            if (string.IsNullOrEmpty(project.InCollaborationWith))
            {
                project.InCollaborationWith = null;
                project.CollaboratorOrganization = null;
                project.CollaboratorContactName = null;
                project.CollaboratorContactEmail = null;
                project.CollaboratorContactPhone = null;
                project.CollaboratorContactMobile = null;
            }
        }

        private static bool UpdateProjectStudents(Project project)
        {
            string studentsBeforeUpdate = GetStudentIDsByProjectID(project.ProjectId);
            
            string studentsAfterUpdate = string.Empty;

            if (project.StudentIDs != null)
            {
                var sb = new StringBuilder();

                foreach (var student in project.StudentIDs.OrderBy(s=> s))
                {
                    if (sb.Length > 0)
                        sb.Append(", ");

                    sb.Append(student);
                }
                studentsAfterUpdate = sb.ToString();
            }

            if (studentsBeforeUpdate != studentsAfterUpdate)
            {

                RemoveProjectStudents(project.ProjectId);

                if (project.StudentIDs != null)
                    foreach (var studentID in project.StudentIDs)
                        AddStudentToProject(project.ProjectId, studentID);

                return true;
            }
            else
                return false;
        }

        public static void SendConfirmation(Project project)
        {
            string subject = "";
            string body = "";

            string bccReceipient = "CseSeniorProject@qu.edu.qa";

            string studentEmails = GetStudentEmails(project.ProjectId);
            string supervisorEmails = GetSupervisorEmails(project.ProjectId);

            if (string.IsNullOrEmpty(studentEmails))
                return;

            //ToDo: Delete these 2 lines
            //studentEmails = "erradi@qu.edu.qa; erradi@qu.edu.qa";
            //supervisorEmails = "erradi@qu.edu.qa";

            subject = string.Format("Your project grades are now accessible (Project# {0})", project.ProjectId);

            var msg = new StringBuilder("Dear Students,\n");

            msg.Append("Your project grades are now accessible @ http://cse.qu.edu.qa/sp .");

            msg.Append("\n\nAll the best.\n");
            msg.Append("Senior Projects Coordinator");

            body = msg.ToString();

            //Send Confirmation Email
            var recipients = new List<String>();
            if (!string.IsNullOrEmpty(studentEmails))
            {
                foreach (var email in studentEmails.Split(';'))
                    recipients.Add(email.Trim());
            }

            var ccRecipients = new List<String>();
            if (!string.IsNullOrEmpty(supervisorEmails))
            {
                foreach (var email in supervisorEmails.Split(';'))
                    ccRecipients.Add(email.Trim());
            }

            //Try to send the email and ignore any errors
            //MailService.SendMail(recipients, ccRecipients, bccReceipient, subject, body);
            try
            {
                MailService.SendMail(recipients, ccRecipients, bccReceipient, subject, body);
            }
            catch { }
        }

        public static void SendProjectRegistrationConfirmation(Project project)
        {
            string subject = "";
            string body = "";

            string bccReceipient = "CseSeniorProject@qu.edu.qa";

            string studentEmails = GetStudentEmails(project.ProjectId);
            string supervisorEmails = GetSupervisorEmails(project.ProjectId);

            if (string.IsNullOrEmpty(studentEmails))
                return;

            //ToDo: Delete these 2 lines
            //studentEmails = "erradi@qu.edu.qa; erradi@qu.edu.qa";
            //supervisorEmails = "erradi@qu.edu.qa";

            if (string.IsNullOrEmpty(supervisorEmails))
                subject = string.Format("Confirmation of Interest to Register for Senior Project - Fall {0}", DateTime.Now.Year);
            else
                subject = string.Format("Senior Project Pre-Registration Confirmation - Fall {0} (ProjectId {1})", DateTime.Now.Year, project.ProjectId);

            var msg = new StringBuilder("Dear Students,\n");
            if (string.IsNullOrEmpty(supervisorEmails))
                msg.AppendFormat("This email confirms that you have registered your intesret for Senior Project. You should form a team and contact one of the available faculty members to discuss a potential senior project. Otherwise the Senior Project Committee will later help allocate you to a group and a supervisor.\n");
            else
                msg.AppendFormat("This email confirms that you are pre-registered for Senior Project - Fall {0}.\n", DateTime.Now.Year);

            msg.AppendFormat("Your ProjectId: {0}. Please include this ProjectId in your enquiry emails.\n", project.ProjectId);
            msg.AppendFormat("Your Project Title: {0}.\n", project.ProjectTitle);

            if (project.SupervisorID != null)
                msg.AppendFormat("Your Project Supervisor: {0}.\n", UserRepository.GetFacultyName(project.SupervisorID.Value));

            msg.Append("You can view/edit the project details by login to http://cse.qu.edu.qa/sp . \n\n");
            msg.AppendFormat("Please note that this pre-registration does not mean automatic registration in the course. You still have to register the course in Banner during the first week of Fall {0} after the department enters the registration approval.\n", DateTime.Now.Year);
            msg.AppendFormat("*** During the first week of Fall {0} you will get an email requesting you to register the course in Banner ***", DateTime.Now.Year);


            msg.Append("\n\nAll the best.\n");
            msg.Append("Senior Projects Coordinator\nCseSeniorProject@qu.edu.qa");

            body = msg.ToString();

            //Send Confirmation Email
            var recipients = new List<String>();
            if (!string.IsNullOrEmpty(studentEmails))
            {
                foreach (var email in studentEmails.Split(';'))
                    recipients.Add(email.Trim());
            }

            var ccRecipients = new List<String>();
            if (!string.IsNullOrEmpty(supervisorEmails))
            {
                foreach (var email in supervisorEmails.Split(';'))
                    ccRecipients.Add(email.Trim());
            }

            //Try to send the email and ignore any errors
            //MailService.SendMail(recipients, ccRecipients, bccReceipient, subject, body);
            try
            {
                MailService.SendMail(recipients, ccRecipients, bccReceipient, subject, body);
            }
            catch { }
        }

        public static int AddStudentToProject(int projectID , string studentID)
        {
            var spDB = new SeniorProjectEntities();
            return spDB.Database.ExecuteSqlCommand("AddStudentToProject @projectID, @studentID",
                 new SqlParameter("projectID", projectID),
                 new SqlParameter("studentID", studentID));
        }

        public static int AddProjectDocument(DocumentSubmission documentSubmission)
        {
            var spDB = new SeniorProjectEntities();
            return spDB.Database.ExecuteSqlCommand("InsertProjectDocument @projectID, @documentType, @documentExtension, @documentUrl, @documentName, @SourceUrl, @submittedBy",
                 new SqlParameter("projectID", documentSubmission.ProjectId),
                 new SqlParameter("documentType", documentSubmission.DocumentType),
                 new SqlParameter("documentExtension", documentSubmission.DocumentExtension),
                 new SqlParameter("documentUrl", documentSubmission.DocumentUrl),
                 new SqlParameter("documentName", documentSubmission.DocumentName),
                 new SqlParameter("sourceUrl", documentSubmission.SourceUrl),
                 new SqlParameter("submittedBy", documentSubmission.SubmittedBy));
        }

        public static int RemoveProjectStudents(int projectID)
        {
            var spDB = new SeniorProjectEntities();
            return spDB.Database.ExecuteSqlCommand("RemoveProjectStudents @projectID",
                 new SqlParameter("projectID", projectID));
        }

        public static bool SetProjectSupervisor(int projectID, int? facultyID, string roleID = "Supervisor")
        {
            var spDB = new SeniorProjectEntities();

            ProjectFaculty projectFaculty = spDB.ProjectFaculties.Where(f => f.ProjectID == projectID
                        && f.RoleID == roleID).FirstOrDefault();

            //Does not exist and no faculty was specified
            if (projectFaculty == null && (facultyID == null || facultyID == 0))
                return false;

            //exists but no change
            if (projectFaculty != null && projectFaculty.FacultyID == facultyID)
                return false;

            //Delete Supervisor if facultyID is 0
            if (projectFaculty != null && (facultyID == null || facultyID == 0))
            {
                    spDB.ProjectFaculties.Remove(projectFaculty);
                    spDB.SaveChanges();
                    return false;
            }

            //If Role already exists delete it first
            if (projectFaculty != null && projectFaculty.FacultyID != facultyID)
                spDB.ProjectFaculties.Remove(projectFaculty);

            var newProjectFaculty = new ProjectFaculty();
            newProjectFaculty.ProjectID = projectID;
            newProjectFaculty.FacultyID = facultyID.Value;
            newProjectFaculty.RoleID = roleID;

            spDB.ProjectFaculties.Add(newProjectFaculty);
            
            spDB.SaveChanges();

            return true;
        }

        //public static void UpdateProject(int projectID, string reviewFeedback)
        //{
        //    var spDB = new SeniorProjectEntities();
        //    Project proj = spDB.Projects.Where(p => p.ProjectId == projectID).FirstOrDefault();

        //    proj.ReviewFeedback = reviewFeedback;
        //    spDB.SaveChanges();
        //}


        public static List<ProjectPeriod> GetProjectPeriods()
        {
            var spDB = new SeniorProjectEntities();
            return spDB.ProjectPeriods.OrderByDescending(p => p.PeriodId).ToList();
        }

        public static int GetDefaultProjectPeriodID()
        {
            var spDB = new SeniorProjectEntities();
            return spDB.ProjectPeriods.Where(p => p.IsDefault == true).FirstOrDefault().PeriodId;
        }

        public static string GetStudentEmails(int projectId)
        {
            var spDB = new SeniorProjectEntities();
            return spDB.Database.SqlQuery<string>(string.Format("Select dbo.GetStudentEmailsByProjectID({0})", projectId)).FirstOrDefault();
        }

        public static string GetSupervisorEmails(int projectId)
        {
            var spDB = new SeniorProjectEntities();
            return spDB.Database.SqlQuery<string>(string.Format("Select dbo.GetSupervisorEmailsByProjectID({0})", projectId)).FirstOrDefault();
        }

        public static bool IsInterimExaminationOpen(int periodId, int projectId)
        {
            var spDB = new SeniorProjectEntities();
            return spDB.Database.SqlQuery<bool>(string.Format("Select dbo.IsProposalExaminationOpen({0}, {1})", periodId, projectId)).FirstOrDefault();
        }

        public static bool IsProjectExaminationOpen(int periodId, int projectId)
        {
            var spDB = new SeniorProjectEntities();
            return spDB.Database.SqlQuery<bool>(string.Format("Select dbo.IsProjectExaminationOpen({0}, {1})", periodId, projectId)).FirstOrDefault();
        }

        //Link to manage a project (e.g., Edit Project, Edit Grades')
        public static List<ActionLink> GetActionLinks(string role, int projectId, int periodId)
        {
            var spDB = new SeniorProjectEntities();
            var results = spDB.Database.SqlQuery<ActionLink>(string.Format("GetActionLinks @Role='{0}', @ProjectId='{1}', @PeriodID={2}", role, projectId, periodId)).ToList();
            return results;
        }

        public static List<ProjectPresentation> GetPresentationsSchedule(string gender)
        {
            var spDB = new SeniorProjectEntities();
            var results = spDB.Database.SqlQuery<ProjectPresentation>(string.Format("GetPresentationsSchedule @Gender='{0}'", gender)).ToList();
            results.ForEach(r => r.FixMultilineFields());
            return results;
        }
    }
}
